﻿// <copyright file="DatabaseManager.cs" company="BerlinSoft">
// Copyright (c) 2009 All Right Reserved
// </copyright>
// <author>Wernfried Schwenkner</author>
// <email>wernfried@schwenkner.de</email>
// <date>2009-10-21</date>
// <summary>
// Contains the DatabaseManager class.
// </summary>

//#define debugDatabase

using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using SenseTimeTracker.Properties;

namespace SenseTimeTracker.Misc
{
	/// <summary>
	/// Class implements the databse manager
	/// </summary>
	public static class DatabaseManager
	{
		/// <summary>
		/// Field contains the data base name
		/// </summary>
		public const string DatabaseName = "TimeTracker.sdf";

		/// <summary>
		/// Field defines the maximiúm person name length
		/// </summary>
		public const int MaxPersontNameLength = 50;

		/// <summary>
		/// Field defines the maximiúm user name length
		/// </summary>
		public const int MaxUserNameLength = 50;

		/// <summary>
		/// Field defines the maximiúm user password length
		/// </summary>
		public const int MaxUserPasswordLength = 60;

		/// <summary>
		/// Field defines the maximiúm client name length
		/// </summary>
		public const int MaxClientNameLength = 30;

		/// <summary>
		/// Field defines the maximiúm client type length
		/// </summary>
		public const int MaxClientTypeLength = 30;

		/// <summary>
		/// Field defines the maximiúm project name length
		/// </summary>
		public const int MaxProjectNameLength = 30;

		/// <summary>
		/// Field defines the maximiúm project number length
		/// </summary>
		public const int MaxProjectNumberLength = 10;

		/// <summary>
		/// Field defines the maximiúm project type length
		/// </summary>
		public const int MaxProjectTypeLength = 15;

		/// <summary>
		/// Field defines the maximiúm task name length
		/// </summary>
		public const int MaxTaskNameLength = 30;

		/// <summary>
		/// Field defines the maximiúm task number length
		/// </summary>
		public const int MaxTaskNumberLength = 10;

		/// <summary>
		/// Field defines the maximiúm task type length
		/// </summary>
		public const int MaxTaskTypeLength = 30;

		/// <summary>
		/// Field defines the maximiúm task location length
		/// </summary>
		public const int MaxTaskLocationLength = 50;

		/// <summary>
		/// Field defines the maximiúm task description length
		/// </summary>
		public const int MaxTaskDescriptionLength = 500;

		/// <summary>
		/// Field defines the maximiúm task default description length
		/// </summary>
		public const int MaxTaskDefaultDescriptionLength = 50;

		/// <summary>
		/// Field defines the maximiúm time card description length
		/// </summary>
		public const int MaxTimeCardDescriptionLength = 50;

		/// <summary>
		/// Field defines the maximiúm time card description length
		/// </summary>
		public const int MaxTimeCardDetailsLength = 500;

		/// <summary>
		/// Field defines the maximiúm category name length
		/// </summary>
		public const int MaxCategoryNameLength = 30;

		/// <summary>
		/// Field defines the maximiúm category number length
		/// </summary>
		public const int MaxCategoryNumberLength = 10;

		/// <summary>
		/// Field defines the maximiúm category type length
		/// </summary>
		public const int MaxCategoryTypeLength = 15;

		/// <summary>
		/// Field defines the maximiúm holiday name length
		/// </summary>
		public const int MaxHolidayNameLength = 30;

		/// <summary>
		/// A Guid for all clients id
		/// </summary>
		private static readonly Guid allClientsId = new Guid("{99470713-5932-409d-AEC2-E412D41D66DB}");

		/// <summary>
		/// A Guid for all projects id
		/// </summary>
		private static readonly Guid allProjectsId = new Guid("{36CDEAF7-3B7E-4b6d-91A7-B3EFCEF1B0F7}");

		/// <summary>
		/// A Guid for all tasks id
		/// </summary>
		private static readonly Guid allTasksId = new Guid("{1806D79F-3323-4ab6-9B6C-7E81E2DBD0F8}");

		/// <summary>
		/// A Guid for unknown task category id
		/// </summary>
		private static readonly Guid unknownTaskCategoryId = new Guid("{0F592B63-A91A-496f-8033-79B98411F39A}");

		/// <summary>
		/// Gets the application path
		/// </summary>
		private static readonly string applicationPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName);

		/// <summary>
		/// Field contains the database connection
		/// </summary>
		private static readonly SqlCeConnection databaseConnection;

		/// <summary>
		/// Field contains the current version
		/// </summary>
		private static Version version;

		/// <summary>
		/// Initializes static members of the <see cref="DatabaseManager"/> class.
		/// </summary>
		static DatabaseManager()
		{
			if (!File.Exists(Persistance.Instance.DatabaseLocation))
			{
				string databaseLocationDatirectory =
						Environment.GetFolderPath(Environment.SpecialFolder.Personal) +
						Path.DirectorySeparatorChar +
						"SenseTimeTracker";
				Persistance.Instance.DatabaseLocation =
					databaseLocationDatirectory +
					Path.DirectorySeparatorChar +
					DatabaseName;
				databaseConnection = new SqlCeConnection("DataSource=" + Persistance.Instance.DatabaseLocation);
				if (!Directory.Exists(databaseLocationDatirectory))
				{
					Directory.CreateDirectory(databaseLocationDatirectory);
				}

				new SqlCeEngine(databaseConnection.ConnectionString).CreateDatabase();
				databaseConnection.Open();
				new SqlCeCommand(
					"CREATE TABLE Clients (clientId uniqueidentifier PRIMARY KEY, " +
					"clientName nvarchar(" + MaxClientNameLength + "), " +
					"clientType nvarchar(" + MaxClientTypeLength + "))",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"CREATE TABLE Projects (projectId uniqueidentifier PRIMARY KEY, clientId uniqueidentifier REFERENCES Clients(clientId), " +
					"projectName nvarchar(" + MaxProjectNameLength + "), " +
					"projectNumber nvarchar(" + MaxProjectNumberLength + "), " +
					"projectType nvarchar(" + MaxProjectTypeLength + "))",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"CREATE TABLE Persons (personId uniqueidentifier PRIMARY KEY, " +
					"personName nvarchar(" + MaxPersontNameLength + "), " +
					"personUserName nvarchar(" + MaxUserNameLength + "), " +
					"personPassword nvarchar(" + MaxUserPasswordLength + "))",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"CREATE TABLE Tasks (taskId uniqueidentifier PRIMARY KEY, " +
					"taskName nvarchar(" + MaxTaskNameLength + "), " +
					"taskNumber nvarchar(" + MaxTaskNumberLength + "), " +
					"taskType nvarchar(" + MaxTaskTypeLength + "), " +
					"taskEstimate int, taskDescription ntext, taskCompleted bit, projectId uniqueidentifier REFERENCES Projects(projectId), personId uniqueidentifier REFERENCES Persons(personId))",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"CREATE TABLE TimeRecords (timeRecordId uniqueidentifier PRIMARY KEY, " +
					"taskId uniqueidentifier REFERENCES Tasks(taskId), " +
					"personId uniqueidentifier REFERENCES Persons(personId), " +
					"description nvarchar(" + MaxTimeCardDescriptionLength + "), " +
					"details ntext, " +
					"timeRecordStartTime datetime, " +
					"timeRecordFinalTime datetime)",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"CREATE TABLE Version (major int, " +
					"minor int, " +
					"build int)",
					databaseConnection).ExecuteNonQuery();
				new SqlCeCommand(
					"INSERT INTO Version (major, minor, build) VALUES (1, 0, 0)",
					databaseConnection).ExecuteNonQuery();
				databaseConnection.Close();
			}
			else
			{
				databaseConnection = new SqlCeConnection("DataSource=" + Persistance.Instance.DatabaseLocation);
			}

			UpdateDatabase();
		}

		/// <summary>
		/// Gets the common database connection
		/// </summary>
		public static SqlCeConnection DbConnection
		{
			get { return databaseConnection; }
		}

		/// <summary>
		/// Gets the all clients id
		/// </summary>
		public static Guid AllClientsId
		{
			get { return allClientsId; }
		}

		/// <summary>
		/// Gets the all projects id
		/// </summary>
		public static Guid AllProjectsId
		{
			get { return allProjectsId; }
		}

		/// <summary>
		/// Gets the all tasks id
		/// </summary>
		public static Guid AllTasksId
		{
			get { return allTasksId; }
		}

		/// <summary>
		/// Gets the all tasks id
		/// </summary>
		public static Guid UnknownTaskCategoryId
		{
			get { return unknownTaskCategoryId; }
		}

		/// <summary>
		/// Gets the application path
		/// </summary>
		public static string ApplicationPath
		{
			get { return applicationPath; }
		}

		/// <summary>
		/// Gets the current time tracker version
		/// </summary>
		public static Version CurrentVersion
		{
			get { return version; }
		}

		/// <summary>
		/// Updates the database to the current version
		/// </summary>
		public static void UpdateDatabase()
		{
			databaseConnection.Open();
			version = Version.FromDb();
			if (version == new Version(1, 0, 0))
			{
				new SqlCeCommand("ALTER TABLE Tasks ALTER COLUMN taskType nvarchar(" + MaxTaskTypeLength + ")", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("ALTER TABLE Persons ALTER COLUMN personPassword nvarchar(" + MaxUserPasswordLength + ")", databaseConnection).ExecuteNonQuery();
				version = new Version(1, 1, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 1, 0))
			{
				SqlCeTransaction transaction = databaseConnection.BeginTransaction();
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskLockCompleted bit", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("UPDATE Tasks SET taskLockCompleted = 'false'", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("CREATE TABLE TaskCategories (taskCategoryId uniqueidentifier PRIMARY KEY, taskCategoryName nvarchar(" + MaxCategoryNameLength + "), taskCategoryNumber nvarchar(" + MaxCategoryNumberLength + "), taskCategoryType nvarchar(" + MaxCategoryTypeLength + "), taskCategoryEstimate int, taskCategoryCompleted bit, taskCategoryLockCompleted bit)", databaseConnection).ExecuteNonQuery();
				SqlCeCommand command = new SqlCeCommand("SELECT * FROM Tasks", databaseConnection);
				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					if (string.IsNullOrEmpty(reader["taskNumber"].ToString()))
					{
						MessageBox.Show("Task number must not be empty!\rUnable to update database.", "Database update", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
						transaction.Rollback();
						return;
					}

					SqlCeCommand updateCommand = new SqlCeCommand(
						"INSERT INTO TaskCategories (taskCategoryId, taskCategoryName, taskCategoryNumber, taskCategoryType, taskCategoryEstimate, taskCategoryCompleted, taskCategoryLockCompleted) " +
						"SELECT @taskCategoryId, @taskCategoryName, @taskCategoryNumber, @taskCategoryType, '0', 'false', 'false' " +
						"WHERE @taskCategoryNumber NOT IN (SELECT taskCategoryNumber FROM TaskCategories)",
						DbConnection);
					updateCommand.Parameters.Add("@taskCategoryId", Guid.NewGuid());
					updateCommand.Parameters.Add("@taskCategoryName", reader["taskName"]);
					updateCommand.Parameters.Add("@taskCategoryNumber", reader["taskNumber"]);
					updateCommand.Parameters.Add("@taskCategoryType", reader["taskType"]);
					updateCommand.Parameters.Add("@taskCategoryEstimate", 0);
					updateCommand.Parameters.Add("@taskCategoryCompleted", false);
					updateCommand.Parameters.Add("@taskCategoryLockCompleted", false);
					updateCommand.ExecuteNonQuery();
					updateCommand.CommandText = "UPDATE TaskCategories SET " +
						"taskCategoryEstimate = taskCategoryEstimate + @taskCategoryEstimate, " +
						"taskCategoryCompleted = (taskCategoryCompleted | @taskCategoryCompleted), " +
						"taskCategoryLockCompleted = (taskCategoryLockCompleted | @taskCategoryLockCompleted) " +
						"WHERE taskCategoryNumber = @taskCategoryNumber";
					updateCommand.Parameters["@taskCategoryEstimate"].Value = reader["taskEstimate"];
					updateCommand.Parameters["@taskCategoryCompleted"].Value = reader["taskCompleted"];
					updateCommand.Parameters["@taskCategoryLockCompleted"].Value = reader["taskLockCompleted"];
					updateCommand.ExecuteNonQuery();
				}

				transaction.Commit();
				databaseConnection.Close();
				databaseConnection.Open();
				version = new Version(1, 2, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 2, 0))
			{
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskColor int", databaseConnection).ExecuteNonQuery();
				version = new Version(1, 3, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 3, 0))
			{
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskLocation nvarchar(" + MaxTaskLocationLength + ")", databaseConnection).ExecuteNonQuery();
				version = new Version(1, 4, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 4, 0))
			{
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskTerpExportLocation bit", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("UPDATE Tasks SET taskTerpExportLocation = 'false'", databaseConnection).ExecuteNonQuery();
				version = new Version(1, 5, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 5, 0))
			{
#if debugDatabase
				new SqlCeCommand("DROP TABLE Holidays", DbConnection).ExecuteNonQuery();
				new SqlCeCommand("DROP TABLE WorkingTime", DbConnection).ExecuteNonQuery();
				new SqlCeCommand("ALTER TABLE Persons DROP COLUMN dailyworkingtime", databaseConnection).ExecuteNonQuery();
				SqlCeCommand command = new SqlCeCommand("SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS", databaseConnection);
				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					if (reader[2].ToString() == "Tasks" && reader[6].ToString() == "TaskCategories")
					{
						new SqlCeCommand("ALTER TABLE Tasks DROP CONSTRAINT " + reader[3], databaseConnection).ExecuteNonQuery();
					}
				}
				new SqlCeCommand("ALTER TABLE Tasks DROP COLUMN taskCategoryId", databaseConnection).ExecuteNonQuery();
#endif
				new SqlCeCommand("CREATE TABLE Holidays (holidayId uniqueidentifier PRIMARY KEY, personId uniqueidentifier REFERENCES Persons(personId), name nvarchar(" + MaxHolidayNameLength + "), type int, day int, month int, dayofweek int, count int, utilisation int, enabled bit, fixed bit)", DbConnection).ExecuteNonQuery();
				new SqlCeCommand("ALTER TABLE Persons ADD COLUMN dailyworkingtime real", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("UPDATE Persons SET dailyworkingtime = 8.0", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("DROP TABLE TaskCategories", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("CREATE TABLE TaskCategories (taskCategoryId uniqueidentifier PRIMARY KEY, taskCategoryName nvarchar(" + MaxClientNameLength + "))", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("INSERT INTO TaskCategories (taskCategoryId, taskCategoryName) VALUES ('" + unknownTaskCategoryId + "', 'Unknown')", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskCategoryId uniqueidentifier REFERENCES TaskCategories(taskCategoryId)", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN taskDefaultDescription nvarchar(" + MaxTaskDefaultDescriptionLength + ")", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("UPDATE Tasks SET taskCategoryId='" + unknownTaskCategoryId + "'", databaseConnection).ExecuteNonQuery();
				new SqlCeCommand("CREATE TABLE WorkingTime (workingTimeId uniqueidentifier PRIMARY KEY, personId uniqueidentifier REFERENCES Persons(personId), validFrom datetime, workingTime real)", DbConnection).ExecuteNonQuery();

				version = new Version(1, 6, 0);
				version.UpdateVersion();
			}

			if (version == new Version(1, 6, 0))
			{
#if debugDatabase
				new SqlCeCommand("ALTER TABLE Tasks DROP COLUMN pimId", DbConnection).ExecuteNonQuery();
#endif
				new SqlCeCommand("ALTER TABLE Tasks ADD COLUMN pimId int", DbConnection).ExecuteNonQuery();
				version = new Version(1, 7, 0);
				version.UpdateVersion();
			}

			databaseConnection.Close();
		}

		public static string ClientName(Guid clientId)
		{
			if (clientId == AllClientsId)
			{
				return Resources.AllSpecifier;
			}

			SqlCeCommand command = new SqlCeCommand("SELECT clientName FROM Clients WHERE clientId = @clientId", databaseConnection);
			command.Parameters.Add("@clientId", clientId);
			databaseConnection.Open();
			string name;
			try
			{
				name = command.ExecuteScalar().ToString();
			}
			catch
			{
				name = string.Empty;
			}

			databaseConnection.Close();
			return name;
		}

		public static string ProjectName(Guid projectId)
		{
			if (projectId == AllProjectsId)
			{
				return Resources.AllSpecifier;
			}

			SqlCeCommand command = new SqlCeCommand("SELECT projectName FROM Projects WHERE projectId = @projectId", databaseConnection);
			command.Parameters.Add("@projectId", projectId);
			databaseConnection.Open();
			string name;
			try
			{
				name = command.ExecuteScalar().ToString();
			}
			catch
			{
				name = string.Empty;
			}

			databaseConnection.Close();
			return name;
		}

		public static string TaskName(Guid taskId)
		{
			if (taskId == AllTasksId)
			{
				return Resources.AllSpecifier;
			}

			SqlCeCommand command = new SqlCeCommand("SELECT taskName FROM Tasks WHERE taskId = @taskId", databaseConnection);
			command.Parameters.Add("@ptaskId", taskId);
			databaseConnection.Open();
			string name;
			try
			{
				name = command.ExecuteScalar().ToString();
			}
			catch
			{
				name = string.Empty;
			}

			databaseConnection.Close();
			return name;
		}

		/// <summary>
		/// Copies the client record in database
		/// </summary>
		/// <param name="oldClientId">Client id to be copied</param>
		/// <returns>Id of the newly created client</returns>
		public static Guid ClientCopy(Guid oldClientId)
		{
			Guid newClientId = Guid.NewGuid();
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO Clients (clientId, clientName, clientType) " +
				"SELECT @newClientId, clientName, clientType " +
				"FROM Clients " +
				"WHERE clientId = @oldClientId",
				DbConnection);
			command.Parameters.Add("@newClientId", newClientId);
			command.Parameters.Add("@oldClientId", oldClientId);
			databaseConnection.Open();
			command.ExecuteNonQuery();
			databaseConnection.Close();
			return newClientId;
		}

		/// <summary>
		/// Gets a list of all projects of a given client
		/// </summary>
		/// <param name="clientId">The client id</param>
		/// <returns>The projects of the given client</returns>
		public static List<Guid> ClientAllProjects(Guid clientId)
		{
			List<Guid> projectIds = new List<Guid>();
			SqlCeCommand command = new SqlCeCommand(
				"SELECT projectId FROM Projects WHERE Projects.clientId = @clientId",
				databaseConnection);
			command.Parameters.Add("@clientId", clientId);
			databaseConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				projectIds.Add(new Guid(reader["projectId"].ToString()));
			}

			databaseConnection.Close();
			return projectIds;
		}

		/// <summary>
		/// Copies a project
		/// </summary>
		/// <param name="oldProjectId">The project to be copied</param>
		/// <param name="clientId">The client id the newly created project shall be assigned</param>
		/// <returns>The created project id</returns>
		//public static Guid ProjectCopy(Guid oldProjectId, Nullable<Guid> clientId)
		//{
		//  Guid newProjectId = Guid.NewGuid();
		//  SqlCeCommand command = new SqlCeCommand(
		//    "INSERT INTO Projects " +
		//    "(projectId, clientId, projectName, projectNumber, projectType) " +
		//    "SELECT @newProjectId, " + (clientId.HasValue ? "@" : string.Empty) + "clientId, projectName, projectNumber, projectType " +
		//    "FROM Projects " +
		//    "WHERE projectId = @oldProjectId",
		//    DbConnection);
		//  command.Parameters.Add("@newProjectId", newProjectId);
		//  command.Parameters.Add("@oldProjectId", oldProjectId);
		//  if (clientId.HasValue)
		//  {
		//    command.Parameters.Add("@clientid", clientId.Value);
		//  }

		//  databaseConnection.Open();
		//  command.ExecuteScalar();
		//  databaseConnection.Close();
		//  return newProjectId;
		//}

		/// <summary>
		/// Gets all tasks according to the given project
		/// </summary>
		/// <param name="projectId">Project id for filtering</param>
		/// <returns>All tasks belonging to the given project</returns>
		public static List<Guid> ProjectAllTasks(Guid projectId)
		{
			List<Guid> tasksIds = new List<Guid>();
			SqlCeCommand command = new SqlCeCommand(
				"SELECT taskId FROM Tasks WHERE Tasks.projectId = @projectId",
				DbConnection);
			command.Parameters.Add("@projectId", projectId);
			databaseConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				tasksIds.Add(new Guid(reader["taskId"].ToString()));
			}

			databaseConnection.Close();
			return tasksIds;
		}

		/// <summary>
		/// Copies a task
		/// </summary>
		/// <param name="oldTaskId">The task to be copied</param>
		/// <param name="projectId">The project id the newly created task shall be assigned</param>
		/// <returns>The created tadk id</returns>
		public static Guid TaskCopy(Guid oldTaskId, Nullable<Guid> projectId)
		{
			Guid newTaskId = Guid.NewGuid();
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO Tasks " +
				"(taskId, projectId, personId, taskName, taskNumber, taskType, taskDescription, taskDefaultDescription, taskEstimate, taskCompleted, taskLockCompleted, taskColor, taskLocation, taskTerpExportLocation, taskCategoryId) " +
								"SELECT @newTaskId, " + (projectId.HasValue ? "@" : string.Empty) + "projectId, personId, taskName, taskNumber, taskType, taskDescription, taskDefaultDescription, taskEstimate, taskCompleted, taskLockCompleted, taskColor, taskLocation, taskTerpExportLocation, taskCategoryId " +
				"FROM Tasks " +
				"WHERE taskId = @oldTaskId",
				DbConnection);
			command.Parameters.Add("@newTaskId", newTaskId);
			command.Parameters.Add("@oldTaskId", oldTaskId);
			if (projectId.HasValue)
			{
				command.Parameters.Add("@projectId", projectId.Value);
			}

			databaseConnection.Open();
			command.ExecuteNonQuery();
			databaseConnection.Close();
			return newTaskId;
		}
	}
}
